package cat.joronya.supermanager;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import cat.joronya.supermanager.Supermanager.Market;
import cat.joronya.supermanager.Supermanager.MyTeam;
import cat.joronya.supermanager.Supermanager.MyTeamDetail;
import cat.joronya.supermanager.Supermanager.PlayerList;
import cat.joronya.supermanager.Supermanager.PrivateLeague;
import cat.joronya.supermanager.Supermanager.PrivateLeagueTeam;
import cat.joronya.supermanager.Supermanager.Home;

/**
 * This class helps open, create, and upgrade the database file.
 */
public class DatabaseHelper extends SQLiteOpenHelper 
{
	public static final String TAG = "supermanager";

	// actual database version
	private static final int DATABASE_VERSION = 8;

	public static final String DATABASE_NAME = "Supermanager.db";
	public static final String PRIVATE_LEAGUE_TABLE_NAME = "private_leagues";
	public static final String PRIVATE_LEAGUE_TEAM_TABLE_NAME = "private_league_team";
	public static final String MARKET_TABLE_NAME = "market";
	public static final String MY_TEAM_TABLE_NAME = "my_teams";
	public static final String MY_TEAM_DETAIL_TABLE_NAME = "my_teams_detail";
	public static final String PLAYERS_LISTS_TABLE_NAME = "players_lists";
	public static final String HOME_TABLE_NAME = "home";

	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	/**
	 * Clean database install. All upgrades included.
	 */
	@Override
	public void onCreate(SQLiteDatabase db) 
	{
		db.execSQL("CREATE TABLE " + PRIVATE_LEAGUE_TABLE_NAME + " ("
				+ PrivateLeague._ID + " INTEGER PRIMARY KEY,"
				+ PrivateLeague.ID_PRIV + " INTEGER NOT NULL,"
				+ PrivateLeague.NAME + " TEXT NOT NULL,"
				+ PrivateLeague.NUMBER_OF_TEAMS + " TEXT NOT NULL,"
				+ PrivateLeague.MODIFIED_DATE + " INTEGER,"
				+ "UNIQUE("+PrivateLeague.ID_PRIV+")"
				+ ");");
		db.execSQL("CREATE TABLE " + PRIVATE_LEAGUE_TEAM_TABLE_NAME + " ("
                + PrivateLeagueTeam._ID + " INTEGER PRIMARY KEY,"
                + PrivateLeagueTeam._PRIVATE_LEAGUE_ID + " INTEGER NOT NULL,"
                + PrivateLeagueTeam.ID_PRIV + " INTEGER NOT NULL,"
                + PrivateLeagueTeam.NAME + " TEXT NOT NULL,"
                + PrivateLeagueTeam.DAY + " INTEGER NOT NULL,"
                + PrivateLeagueTeam.MANAGER + " TEXT NOT NULL,"
                + PrivateLeagueTeam.CITY + " TEXT NOT NULL,"
                + PrivateLeagueTeam.POINTS + " FLOAT NOT NULL,"
                + PrivateLeagueTeam.GENERAL + " FLOAT NOT NULL,"
                + PrivateLeagueTeam.BROKER + " INTEGER NOT NULL,"
                + PrivateLeagueTeam.MODIFIED_DATE + " INTEGER,"
                + "UNIQUE("+PrivateLeagueTeam.ID_PRIV+","+PrivateLeagueTeam.NAME+","+PrivateLeagueTeam.DAY+")"
                + ");");
		db.execSQL("CREATE TABLE " + MARKET_TABLE_NAME + " ("
                + Market._ID + " INTEGER PRIMARY KEY,"
                + Market.ID_PLAYER + " TEXT NOT NULL,"
                + Market.CODI_PLAYER + " TEXT NOT NULL,"
                + Market.POSITION + " TEXT NOT NULL,"
                + Market.STATUS + " TEXT,"
                + Market.NACIONALITY + " TEXT,"
                + Market.NAME + " TEXT NOT NULL,"
                + Market.DAY + " INTEGER NOT NULL,"
                + Market.INFO + " TEXT,"
                + Market.TEAM + " TEXT NOT NULL,"
                + Market.BALANCE + " TEXT NOT NULL,"
                + Market.VS_TEAM + " TEXT,"
                + Market.AVERAGE + " FLOAT NOT NULL,"
                + Market.PRICE + " INTEGER NOT NULL,"
                + Market.MINUTES + " TEXT NOT NULL,"
                + Market.RATING + " FLOAT NOT NULL,"
                + Market.RATING_LAST3_GAMES + " FLOAT NOT NULL,"
                + Market.INCREASE_15 + " FLOAT NOT NULL,"
                + Market.KEEP_PRICE + " FLOAT NOT NULL,"
                + Market.DECREASE_15 + " FLOAT NOT NULL,"
                + Market.SHOWN_IN_BUY + " INTEGER,"
                + Market.COLOR_IN_BUY + " TEXT,"
                + Market.MODIFIED_DATE + " INTEGER,"
                + "UNIQUE("+Market.ID_PLAYER+","+Market.DAY+")"
                + "UNIQUE("+Market.CODI_PLAYER+","+Market.DAY+")"
                + ");");
		db.execSQL("CREATE TABLE " + MY_TEAM_TABLE_NAME + " ("
				+ MyTeam._ID + " INTEGER PRIMARY KEY,"
				+ MyTeam.ID_EQU + " INTEGER NOT NULL,"
				+ MyTeam.DAY + " INTEGER NOT NULL,"
				+ MyTeam.NAME + " TEXT NOT NULL,"
				+ MyTeam.STATUS + " INTEGER,"
				+ MyTeam.TEAM_PLAYERS + " INTEGER,"
				+ MyTeam.CHANGES_TOTAL + " INTEGER,"
				+ MyTeam.CHANGES_DONE + " INTEGER,"
				+ MyTeam.POINTS + " FLOAT,"
				+ MyTeam.GENERAL + " FLOAT,"
				+ MyTeam.BROKER + " INTEGER,"
				+ MyTeam.POINTS_CLASSIFICATION + " INTEGER,"
				+ MyTeam.GENERAL_CLASSIFICATION + " INTEGER,"
				+ MyTeam.BROKER_CLASSIFICATION + " INTEGER,"
				+ MyTeam.POINTS_BEST + " INTEGER,"
				+ MyTeam.GENERAL_BEST + " INTEGER,"
				+ MyTeam.POINTS_AVERAGE + " FLOAT,"
				+ MyTeam.BEST_CLASSIFICATION + " INTEGER,"
				+ MyTeam.CASH + " INTEGER,"
				+ MyTeam.EARNINGS + " INTEGER,"
				+ MyTeam.AVERAGE_SEASON + " FLOAT,"
				+ MyTeam.AVERAGE_VARIATION + " FLOAT,"
				+ MyTeam.CANCEL_CHANGES + " INTEGER, "
				+ MyTeam.MODIFIED_DATE + " INTEGER,"
				+ "UNIQUE("+MyTeam.ID_EQU+","+MyTeam.DAY+")"
				+ ");");
		db.execSQL("CREATE TABLE " + MY_TEAM_DETAIL_TABLE_NAME + " ("
				+ MyTeamDetail._ID + " INTEGER PRIMARY KEY,"
				+ MyTeamDetail.ID_EQU + " INTEGER NOT NULL,"
				+ MyTeamDetail.DAY + " INTEGER NOT NULL,"
				+ MyTeamDetail.POSITION + " TEXT NOT NULL,"
				+ MyTeamDetail.NUM_POSITION + " INTEGER NOT NULL,"
				+ MyTeamDetail.SELL + " INTEGER, "
				+ MyTeamDetail.BUY + " INTEGER, "
				+ MyTeamDetail.CANCEL + " INTEGER, "
				+ MyTeamDetail.CHANGE + " INTEGER, "
                + MyTeamDetail.CODI_PLAYER + " TEXT,"
				+ MyTeamDetail.COST + " INTEGER,"
				+ MyTeamDetail.SIGNING_DAY + " INTEGER,"
				+ MyTeamDetail.PRICE + " FLOAT,"
				+ MyTeamDetail.WIN + " INTEGER,"
				+ MyTeamDetail.RATING + " FLOAT,"
				+ MyTeamDetail.POINTS + " INTEGER,"
				+ MyTeamDetail.REBOUNDS + " INTEGER,"
				+ MyTeamDetail.THREE + " INTEGER,"
				+ MyTeamDetail.ASSISTS + " INTEGER,"
				+ MyTeamDetail.RATING_AVERAGE + " FLOAT,"
				+ MyTeamDetail.POINTS_AVERAGE + " FLOAT,"
				+ MyTeamDetail.REBOUNDS_AVERAGE + " FLOAT,"
				+ MyTeamDetail.THREE_AVERAGE + " FLOAT,"
				+ MyTeamDetail.ASSISTS_AVERAGE + " FLOAT,"
				+ MyTeamDetail.MODIFIED_DATE + " INTEGER,"
				+ MyTeamDetail.STATUS + " TEXT,"
                + MyTeamDetail.NACIONALITY + " TEXT,"
                + MyTeamDetail.NAME + " TEXT NOT NULL,"
                + MyTeamDetail.TEAM + " TEXT NOT NULL,"
                + MyTeamDetail.VS_TEAM + " TEXT,"
                + MyTeamDetail.INFO + " TEXT,"
                + "UNIQUE("+MyTeamDetail.ID_EQU+","+MyTeamDetail.DAY+","+MyTeamDetail.POSITION+")"
				+ ");");
		db.execSQL("CREATE TABLE " + HOME_TABLE_NAME + " ("
				+ Home._ID + " INTEGER PRIMARY KEY,"
				+ Home.DAY + " INTEGER NOT NULL,"
				+ Home.NEXT_DAY + " INTEGER NOT NULL,"
				+ Home.STATUS + " INTEGER,"
				+ Home.INFO + " TEXT,"
				+ Home.DUE_TIME + " INTEGER,"
				+ Home.MODIFIED_DATE + " INTEGER,"
				+ "UNIQUE("+Home.DAY+")"
				+ ");");
	}

	/**
	 * Starting from oldVersion installed apply all upgrades in all versions
	 * to newVersion.
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
	{
		Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
				+ newVersion);
		
		for( int i=oldVersion+1; i<=newVersion; i++ )
		{
			if( i == 2 )
				upgradeToVersion2(db);
			else if( i == 3 )
				upgradeToVersion3(db);
			else if( i == 4 )
				upgradeToVersion4(db);
			else if( i == 5 )
				upgradeToVersion5(db);
			else if( i == 6 )
				upgradeToVersion6(db);
			else if( i == 7 )
				upgradeToVersion7(db);
			else if( i == 8 )
				upgradeToVersion8(db);
		}
	}

	/**
	 * Update from version #1 to version #2.
	 * Nothing to do.
	 * @param db
	 */
	private void upgradeToVersion2(SQLiteDatabase db)
	{
		;
	}
	
	/**
	 * Update from version #2 to version #3.
	 * Adding vs_team field to market table.
	 */
	private void upgradeToVersion3(SQLiteDatabase db)
	{
		Log.d(TAG, "Upgrading database from version 2 to version 3.");
		
		// add vs_team field on market
		db.execSQL("ALTER TABLE "+MARKET_TABLE_NAME+" ADD COLUMN "+Market.VS_TEAM+" TEXT;");
	}
	
	/**
	 * Update from version #3 to version #4.
	 * Adding my_team_detail fields.
	 */
	private void upgradeToVersion4(SQLiteDatabase db)
	{
		Log.d(TAG, "Upgrading database from version 3 to version 4.");
		
		// add vs_team field on market
		db.execSQL("ALTER TABLE "+MY_TEAM_DETAIL_TABLE_NAME+" ADD COLUMN "+MyTeamDetail.STATUS + " TEXT;");
		db.execSQL("ALTER TABLE "+MY_TEAM_DETAIL_TABLE_NAME+" ADD COLUMN "+MyTeamDetail.NACIONALITY + " TEXT;");
		db.execSQL("ALTER TABLE "+MY_TEAM_DETAIL_TABLE_NAME+" ADD COLUMN "+MyTeamDetail.NAME + " TEXT NOT NULL DEFAULT '';");
		db.execSQL("ALTER TABLE "+MY_TEAM_DETAIL_TABLE_NAME+" ADD COLUMN "+MyTeamDetail.TEAM + " TEXT NOT NULL DEFAULT '';");
		db.execSQL("ALTER TABLE "+MY_TEAM_DETAIL_TABLE_NAME+" ADD COLUMN "+MyTeamDetail.VS_TEAM + " TEXT;");
		db.execSQL("ALTER TABLE "+MY_TEAM_DETAIL_TABLE_NAME+" ADD COLUMN "+MyTeamDetail.INFO + " TEXT;");
	}
	
	/**
	 * Update from version #4 to version #5.
	 * Adding changes total and done.
	 * @param db
	 */
	private void upgradeToVersion5(SQLiteDatabase db)
	{
		Log.d(TAG, "Upgrading database from version 4 to version 5.");
		
		// add changes done and total
		db.execSQL("ALTER TABLE "+MY_TEAM_TABLE_NAME+" ADD COLUMN "+MyTeam.CHANGES_TOTAL + " INTEGER;");
		db.execSQL("ALTER TABLE "+MY_TEAM_TABLE_NAME+" ADD COLUMN "+MyTeam.CHANGES_DONE + " INTEGER;");
	}
	
	/**
	 * Update from version #5 to version #6.
	 * Data reset from season 2011-12 to new one 2012-13.
	 * @param db
	 */
	private void upgradeToVersion6(SQLiteDatabase db)
	{
		Log.d(TAG, "Upgrading database from version 5 to version 6.");
		
		// add changes done and total
		db.execSQL("DELETE FROM "+PRIVATE_LEAGUE_TABLE_NAME+";");
		db.execSQL("DELETE FROM "+PRIVATE_LEAGUE_TEAM_TABLE_NAME+";");
		db.execSQL("DELETE FROM "+MARKET_TABLE_NAME+";");
		db.execSQL("DELETE FROM "+MY_TEAM_TABLE_NAME+";");
		db.execSQL("DELETE FROM "+MY_TEAM_DETAIL_TABLE_NAME+";");
		db.execSQL("DELETE FROM "+HOME_TABLE_NAME+";");
	}
	
	/**
	 * Update from version #6 to version #7.
	 * Data reset from season 2012-13 to new one 2013-14.
	 * @param db
	 */
	private void upgradeToVersion7(SQLiteDatabase db)
	{
		Log.d(TAG, "Upgrading database from version 6 to version 7.");
		
		// add changes done and total
		db.execSQL("DELETE FROM "+PRIVATE_LEAGUE_TABLE_NAME+";");
		db.execSQL("DELETE FROM "+PRIVATE_LEAGUE_TEAM_TABLE_NAME+";");
		db.execSQL("DELETE FROM "+MARKET_TABLE_NAME+";");
		db.execSQL("DELETE FROM "+MY_TEAM_TABLE_NAME+";");
		db.execSQL("DELETE FROM "+MY_TEAM_DETAIL_TABLE_NAME+";");
		db.execSQL("DELETE FROM "+HOME_TABLE_NAME+";");
	}
	
	/**
	 * Update from version #7 to version #8.
	 * Added players_lists table. Used first for favorites.
	 * @param db
	 */
	private void upgradeToVersion8(SQLiteDatabase db)
	{
		Log.d(TAG, "Upgrading database from version 7 to version 8.");
		
		// adding player list table
		db.execSQL("CREATE TABLE " + PLAYERS_LISTS_TABLE_NAME + " ("
			+ PlayerList._ID + " INTEGER PRIMARY KEY,"
			+ PlayerList.CODI_PLAYER + " TEXT,"
			+ PlayerList.LIST_NAME + " TEXT,"
			+ PlayerList.MODIFIED_DATE + " INTEGER,"
			+ "UNIQUE("+PlayerList.CODI_PLAYER+","+PlayerList.LIST_NAME+")"
			+ ");");
	}
}